Self Joins

SQL has traditionally been a poor tool for analysing data in context. ie. Selecting information about a row that requires input from neighboring rows. For instance, to select information about the highest salary earner in each department, we must first find the employees with the highest salary, and then select their details:

SELECT *
FROM   emp
WHERE  (dept_no, sal) in (
	SELECT dept_no, max(sal)
	FROM   emp
	GROUP BY dept_no)

Queries that are context sensitive have one thing in common: they must refer to the same table more than once. The example above is remarkably simple; more complex queries must access the same table 3, 4 or more times.

Oracle 8i has supplied a new feature called Analytic Functions which are unique in SQL: they allow a row to access information from its neighbors without having to perform a self-join. Analytic functions are difficult to get the hang of, but once mastered are quite easy and even intuitive! See the Oracle SQL Reference for more information on Analytic Functions.

The query above can be re-written with analytic functions as follows:

SELECT *
FROM   (
       SELECT a.*
       ,      max(sal) OVER(PARTITION BY dept_no) AS max_sal
       FROM   emp a
       )
WHERE  sal = max_sal

Be aware that Analytic Functions are not necessarily quicker than self joins. Even though they seem to perform fewer and more simple steps, queries like the one above can be more efficient when written as a self join. Always trial both option to find the fastest.

If only a single result is required (eg. One employee with highest salary, rather than all with that salary) then it is even faster to use the KEEP modifier to the aggregate function.

SELECT max(ename) KEEP (DENSE_RANK LAST ORDER BY sal), max(sal)
FROM   emp a

Another option is to process the data in a procedural language such as PL/SQL. eg.

DECLARE
    CURSOR c1 IS
    SELECT *
    FROM   emp
    ORDER BY dept_no, sal

    prev_row c1_rec%rowtype;
BEGIN
    FOR this_row IN c1 LOOP
        IF (prev_row.dept_no IS NULL) THEN
            prev_row := this_row;
        END IF;

        IF (this_row.dept_no != high_row.dept_no) THEN
            do_whatever(high_row);
        END IF;
    END LOOP;

    IF (high_row.dept_no IS NOT NULL) THEN
        do_whatever(high_row);
    END IF;
END;